import pandas as pd
import pymysql
from sqlalchemy import create_engine
import sys


# 用户用电数据，数据量大，批量插入
def import_dl():
	IO = '1.xlsx'
	sheet = pd.read_excel(io=IO)
	df = pd.DataFrame(sheet)
	# 打开数据库连接
	db = pymysql.connect("47.96.13.76", "root", "Sx3jdCwhCZAmxWie", "python_cs", charset='utf8mb4')
	# 使用cursor()方法获取操作游标 
	cursor = db.cursor()
	values = []
	# 总数
	count = df.count().CONS_NAME
	i = 1
	for index,row in df.iterrows():
		# print(getattr(row, "CONS_NAME"))
		print(index,row.CONS_NAME)
		value = (row.CONS_NAME, row.CONS_NO, row.SEND_DATE.strftime('%Y-%m-%d %H:%M:%S'), row.T_AMT, row.T_CAT_KWH_AMT, row.T_PL_AMT, row.T_SETTLE_PQ, row.YM)
		values.append(value)
		# 每 500 条插入一次
		if i >= 500 or i == count:
			print(values)
			sql = "INSERT INTO dlsj(CONS_NAME, CONS_NO, SEND_DATE, T_AMT, T_CAT_KWH_AMT, T_PL_AMT, T_SETTLE_PQ, YM) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
			try:
				# 执行sql语句
				cursor.executemany(sql,values)
				# 提交到数据库执行
				db.commit()
				print(True)
				# 清空数组与计数
				values = []
				i = 0
			except Exception as err:
				# Rollback in case there is any error
				db.rollback()
				print("Error %s for execute sql: %s" % (err, sql))
		i = i + 1
	# 关闭数据库连接
	db.close()


# 执行导入操作
# import_dl()


# 用户用电数据，数据量大，单次插入慢
def import_dl2():
	IO = '1111.xlsx'
	sheet = pd.read_excel(io=IO,nrows = 10)
	df = pd.DataFrame(sheet)
	# 打开数据库连接
	db = pymysql.connect("47.96.13.76", "root", "Sx3jdCwhCZAmxWie", "python_cs", charset='utf8mb4')
	# 使用cursor()方法获取操作游标 
	cursor = db.cursor()
	values = []
	for row in df.itertuples():
		# print(getattr(row, "CONS_NAME"))
		print(row.CONS_NAME)
		sql = "INSERT INTO dlsj(CONS_NAME, CONS_NO, SEND_DATE, T_AMT, T_CAT_KWH_AMT, T_PL_AMT, T_SETTLE_PQ, YM) VALUES (%s, %d, str_to_date(\'%s\','%%Y-%%m-%%d %%H:%%i:%%s'), %d, %d, %d, %d, %s)" % ('"' + row.CONS_NAME + '"', row.CONS_NO, row.SEND_DATE, row.T_AMT, row.T_CAT_KWH_AMT, row.T_PL_AMT, row.T_SETTLE_PQ, row.YM)
		try:
			# 执行sql语句
			cursor.execute(sql)
			# 提交到数据库执行
			db.commit()
			print(True)
		except Exception as err:
			# Rollback in case there is any error
			db.rollback()
			print("Error %s for execute sql: %s" % (err, sql))
	# 关闭数据库连接
	db.close()



# 导入换热站数据
def import_addr(path):
	IO2 = path # 2.xls
	sheet2 = pd.read_excel(io=IO2,sheet_name=None,nrows = 10)

	for n in sheet2.keys():
		print(n)
		# df2 = pd.DataFrame(sheet2['' + n + ''])
		# print(df2)
		# for row in df2.itertuples():
		# 	print(getattr(row, "单位地址"))


import_addr(sys.argv[1])
print('success')













